{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Vessels making voyages\n",
    "The `voyages` table contains top level information about a voyage from one port to another, including when and where the voyage started and ended, and which vessel was involved in the voyage. You can use this information to identify which vessels made a voyage from one port to another in some time range:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from descarteslabs.vektorius import vector\n",
    "from ipyleaflet import Map, GeoJSON\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "ports = vector.table(\"ports\")\n",
    "voyages = vector.table(\"voyages\")\n",
    "\n",
    "# find geometry of start/end ports  that we care about\n",
    "origin_port_name = \"PORT DE SALVADOR\"\n",
    "dest_port_name = \"PORTO DE SUAPE\"\n",
    "ports = ports.filter(ports.port_name.isin([origin_port_name, dest_port_name]))\n",
    "port_geoms = ports[ports.port_name, ports.port_geom].execute()\n",
    "start_port_geom = port_geoms[port_geoms.port_name == origin_port_name].port_geom.iloc[0]\n",
    "end_port_geom = port_geoms[port_geoms.port_name == dest_port_name].port_geom.iloc[0]\n",
    "\n",
    "# because we store Geographies as geodesics, we need to force these literal shapes\n",
    "# to be treated as such\n",
    "start_port_geom.crs = \"EPSG:4326\"\n",
    "end_port_geom.crs = \"EPSG:4326\"\n",
    "\n",
    "# find voyages that start or end within timeframe\n",
    "start_date = \"2018-01-01\"\n",
    "end_date = \"2018-12-31\"\n",
    "dt_filter = (voyages.departure.between(start_date, end_date) |\n",
    "             voyages.arrival.between(start_date, end_date))\n",
    "    \n",
    "# voyage should be between the two ports you care about\n",
    "spatial_filter = (voyages.origin.intersects(start_port_geom) &\n",
    "                  voyages.destination.intersects(end_port_geom))\n",
    "    \n",
    "voyages = voyages.filter(dt_filter & spatial_filter)\n",
    "df = voyages[voyages.mmsi, voyages.linestring].execute()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we'll put this on a map to see what the voyages look like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a map with ipyleaflet\n",
    "m = Map(center=(15.453680224345835, -23.466796875000004), zoom=3)\n",
    "\n",
    "# __geo_interface__ allows us to get the GeoJSON FeatureCollection\n",
    "# representation of the GeoSeries\n",
    "geo_json = GeoJSON(data=df.linestring.__geo_interface__)\n",
    "m.add_layer(geo_json)\n",
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also estimate how much cargo in kg was transported from one port to another in each month. You can use functions to alter column data to achieve this analysis before fetching any of the data locally:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vessels = vector.table(\"vessels\")\n",
    "\n",
    "joined = voyages.inner_join(vessels, vessels.mmsi == voyages.mmsi)\n",
    "\n",
    "capacity = joined[\n",
    "    # get vessel capacity in kg\n",
    "    (vessels.capacity.dwt * 1000).name(\"vessel_capacity_in_kg\"),\n",
    "    # get the departure month for nicer grouping\n",
    "    voyages.departure.month().name(\"departure_month\")\n",
    "]\n",
    "grouped = capacity.group_by(capacity.departure_month)\n",
    "grouped = grouped.aggregate(capacity.vessel_capacity_in_kg\n",
    "                            .sum()\n",
    "                            .name(\"est_cargo_weight_in_kg\"))\n",
    "grouped = grouped.sort_by(grouped.departure_month)\n",
    "df = grouped.execute()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
